package com.fpt.quanlysanbong.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.fpt.quanlysanbong.domain.Account;
import com.fpt.quanlysanbong.domain.Person;
import com.fpt.quanlysanbong.domain.RoleDictionary;

public class PersonDao extends BaseDao {

	public Account getAccount(String username, String password) {
		Account acc = null;
		String sql = "SELECT * FROM dbo.Account WHERE status = 1 and username = '" + username + "' and password ='" + password + "'";
		try {
			rs = stmt.executeQuery(sql);

			if (rs.next()) {
				acc = new Account();
				acc.setId(rs.getInt("id"));
				acc.setUsername(username);
				acc.setPassword(password);
				acc.setStatus(rs.getBoolean("status"));
				int roleId = rs.getInt("roleId");
				int personId = rs.getInt("personId");
				acc.setRole(getRoleDictionaryById(roleId));
				acc.setPerson(getPersonById(personId));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 

		return acc;
	}

	public Account getAccountById(int id) {
		Account acc = null;
		String sql = "SELECT * FROM dbo.Account WHERE id = " + id;
		try {
			rs = stmt.executeQuery(sql);

			if (rs.next()) {
				acc = new Account();
				acc.setId(rs.getInt("id"));
				acc.setUsername(rs.getString("username"));
				acc.setPassword(rs.getString("password"));
				acc.setStatus(rs.getBoolean("status"));
				int roleId = rs.getInt("roleId");
				int personId = rs.getInt("personId");
				acc.setRole(getRoleDictionaryById(roleId));
				acc.setPerson(getPersonById(personId));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 

		return acc;
	}
	
	public Account getAccountByPersonId(int personId) {
		Account acc = null;
		String sql = "SELECT * FROM dbo.Account WHERE personId = " + personId;
		try {
			rs = stmt.executeQuery(sql);

			if (rs.next()) {
				acc = new Account();
				acc.setId(rs.getInt("id"));
				acc.setUsername(rs.getString("username"));
				acc.setPassword(rs.getString("password"));
				acc.setStatus(rs.getBoolean("status"));
				int roleId = rs.getInt("roleId");
				acc.setRole(getRoleDictionaryById(roleId));
				acc.setPerson(getPersonById(personId));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 

		return acc;
	}

	public Person getPersonById(int id) {
		Person person = null;
		String sql = "Select * From Person WHERE id = " + id;
		try {
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				person = new Person();
				person.setId(rs.getInt("id"));
				person.setName(rs.getString("name"));
				person.setAddress(rs.getString("address"));
				person.setEmail(rs.getString("email"));
				person.setPhone(rs.getString("phone"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 

		return person;
	}

	public RoleDictionary getRoleDictionaryById(int id) {
		RoleDictionary role = null;
		String sql = "SELECT * FROM dbo.RoleDictionary where id = " + id;
		try {
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				role = new RoleDictionary();
				role.setId(rs.getInt("id"));
				role.setName(rs.getString("name"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return role;
	}

	public boolean addNewAccount(Account acc) {
		boolean success = false;

		String sql1 = "Insert into Person(name, email, address, phone) values (?,?,?,?)";
		String sql2 = "insert into Account(username,password, personId, roleId) values(?,?,?,?)";
		String sql4 = "update SoccerField set staffId = ? where id = ?";

		try {
			conn.setAutoCommit(false);
			prst = conn.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
			prst.setString(1, acc.getPerson().getName());
			prst.setString(2, acc.getPerson().getEmail());
			prst.setString(3, acc.getPerson().getAddress());
			prst.setString(4, acc.getPerson().getPhone());

			int affectedRows = prst.executeUpdate();
			if (affectedRows == 0) {
				throw new SQLException("Creating user failed, no rows affected.");
			}

			rs = prst.getGeneratedKeys();
			if (rs.next()) {
				acc.getPerson().setId(rs.getInt(1));
			} else {
				throw new SQLException("Creating user failed, no rows affected.");
			}

			prst = conn.prepareStatement(sql2);
			prst.setString(1, acc.getUsername());
			prst.setString(2, acc.getPassword());
			prst.setInt(3, acc.getPerson().getId());
			prst.setInt(4, acc.getRole().getId());
			prst.executeUpdate();

			if (acc.getPerson().getSoccerFieldId() != 0) {
				prst = conn.prepareStatement(sql4);
				prst.setInt(1, acc.getPerson().getId());
				prst.setInt(2, acc.getPerson().getSoccerFieldId());
				prst.executeUpdate();
			}

			conn.commit();
			success = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} 

		return success;
	}

	public boolean updateAccount(Account acc) {
		boolean success = false;
		String sql1 = "update p set p.name = ?, p.email = ?, p.address = ?, p.phone = ? from Person p inner join Account acc on p.id = acc.personid where acc.id = ?";

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(sql1);
			prst.setString(1, acc.getPerson().getName());
			prst.setString(2, acc.getPerson().getEmail());
			prst.setString(3, acc.getPerson().getAddress());
			prst.setString(4, acc.getPerson().getPhone());
			prst.setInt(5, acc.getId());

			prst.executeUpdate();
			conn.commit();
			success = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return success;
	}

	public boolean changePassword(Account acc) {
		boolean success = false;
		String sql1 = "update dbo.Account set password = ? where id = ?";

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(sql1);
			prst.setString(1, acc.getPassword());
			prst.setInt(2, acc.getId());

			prst.executeUpdate();
			conn.commit();
			success = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} 

		return success;
	}
}
